/**
 *  보유비중은  각주체별 매집수량  / 총 누적 매집수량  
 *  선도비중은  각주체별 매집고점 / 총 매집고 
 */
//모듈을 추출합니다.
var mysql = require('mysql');


var connection = mysql.createConnection({
	host : "54.178.215.219",
	user : "istock",
	password : "!Q2w3e??",
	database : "uds"
});


var query = ' select stock_date, \n' +  
' sum(case investor_type when \'A001000\' THEN purcha_volum else 0 end) as a100,  \n' +
' sum(case investor_type when \'A002001\' THEN purcha_volum else 0 end) as a201,  \n' +
' sum(case investor_type when \'A002002\' THEN purcha_volum else 0 end) as a202,  \n' +
' sum(case investor_type when \'A002003\' THEN purcha_volum else 0 end) as a203,  \n' +
' sum(case investor_type when \'A002004\' THEN purcha_volum else 0 end) as a204,  \n' +
' sum(case investor_type when \'A002005\' THEN purcha_volum else 0 end) as a205,  \n' +
' sum(case investor_type when \'A002006\' THEN purcha_volum else 0 end) as a206,  \n' +
' sum(case investor_type when \'A002007\' THEN purcha_volum else 0 end) as a207,  \n' +
' sum(case investor_type when \'A002008\' THEN purcha_volum else 0 end) as a208,  \n' +
' sum(case investor_type when \'A002009\' THEN purcha_volum else 0 end) as a209,  \n' +
' sum(case investor_type when \'A003000\' THEN purcha_volum else 0 end) as a300,  \n' +
' sum(case investor_type when \'A003001\' THEN purcha_volum else 0 end) as a301   \n' +
' from tbl_stock_investor \n ' +
' where stock_cd =  \'115530\' ' + 
'group by stock_date order by stock_date asc'
;

var a_date = [];
var a_a100 = [];
var a_a201 = [];
var a_a202 = [];
var a_a203 = [];
var a_a204 = [];
var a_a205 = [];
var a_a206 = [];
var a_a207 = [];
var a_a208 = [];
var a_a209 = [];
var a_a300 = [];
var a_a301 = [];

var b_a100 = [];
var b_a201 = [];
var b_a202 = [];
var b_a203 = [];
var b_a204 = [];
var b_a205 = [];
var b_a206 = [];
var b_a207 = [];
var b_a208 = [];
var b_a209 = [];
var b_a300 = [];
var b_a301 = [];

var v_a100 = [];
var v_a201 = [];
var v_a202 = [];
var v_a203 = [];
var v_a204 = [];
var v_a205 = [];
var v_a206 = [];
var v_a207 = [];
var v_a208 = [];
var v_a209 = [];
var v_a300 = [];
var v_a301 = [];

var min_100 = 0;
var min_201 = 0;
var min_202 = 0;
var min_203 = 0;
var min_204 = 0;
var min_205 = 0;
var min_206 = 0;
var min_207 = 0;
var min_208 = 0;
var min_209 = 0;
var min_300 = 0;
var min_301 = 0;

var max_100 = 0;
var max_201 = 0;
var max_202 = 0;
var max_203 = 0;
var max_204 = 0;
var max_205 = 0;
var max_206 = 0;
var max_207 = 0;
var max_208 = 0;
var max_209 = 0;
var max_300 = 0;
var max_301 = 0;

var b_total = "";
var max_total = "";



//누적 매수량에서 최저매수량 
function getMinVolum(aObj) {

	var min = aObj[0];
	for (var i = 0; i < aObj.length; i ++) {
		if( min > aObj[i] ) {
			min = aObj[i];
		}
	}

	return min;
}


function getMaxVolum(aObj) {
	var max = aObj[0];
	for (var i = 0; i < aObj.length; i ++) {
		if( aObj[i] > max ) {
			max = aObj[i];
		}
	}

	return max;
}

function getVariance(aObj, maxVal) {	

	var a_tmp = new Array();
	for(var i=0; i < aObj.length; i++) {
		if(aObj[i] == 0) {
			a_tmp[i] = 0;
		}else {
			a_tmp[i] =  Math.round((aObj[i]/maxVal).toFixed(2) * 100);
		}
	}

	return a_tmp;
}

//매집수량 가공법 
function getSumHiVolum(aObj, minVols){
	var a_tmp = new Array();
	for(var i=0; i < aObj.length; i++) {
		a_tmp[i] = aObj[i] - minVols;

		//console.log("sum : " +  aObj[i] + " :"  + a_tmp[i]);
	}

	return a_tmp;
}


function makeData(rows) {

	// 누적 합계 가공법 
	for (var i = 0; i < rows.length; i++) {
		var row = rows[i];
		a_date[i] = row.stock_date;
		if (i> 0) {
			a_a100[i] = row.a100 + a_a100[i-1];
			a_a201[i] = row.a201 + a_a201[i-1];
			a_a202[i] = row.a202 + a_a202[i-1];
			a_a203[i] = row.a203 + a_a203[i-1];
			a_a204[i] = row.a204 + a_a204[i-1];
			a_a205[i] = row.a205 + a_a205[i-1];
			a_a206[i] = row.a206 + a_a206[i-1];
			a_a207[i] = row.a207 + a_a207[i-1];
			a_a208[i] = row.a208 + a_a208[i-1];
			a_a209[i] = row.a209 + a_a209[i-1];
			a_a300[i] = row.a300 + a_a300[i-1];
			a_a301[i] = row.a301 + a_a301[i-1];
		} else {
			a_a100[i] = row.a100;
			a_a201[i] = row.a201;
			a_a202[i] = row.a202;
			a_a203[i] = row.a203;
			a_a204[i] = row.a204;
			a_a205[i] = row.a205;
			a_a206[i] = row.a206;
			a_a207[i] = row.a207;
			a_a208[i] = row.a208;
			a_a209[i] = row.a209;
			a_a300[i] = row.a300;
			a_a301[i] = row.a301;
		}
		//console.log( a_date[i]+ " : " + a_a201[i]);
	}

	min_100 = getMinVolum(a_a100);  // 최고저점 가공법;
	b_a100 = getSumHiVolum(a_a100, min_100); // 매집수량  리턴 
	max_100 = getMaxVolum(b_a100);				// 매집고점 가공법 
	v_a100 = getVariance(b_a100, max_100);		// 분산비율 

	min_201 = getMinVolum(a_a201);
	b_a201 = getSumHiVolum(a_a201, min_201);
	max_201 = getMaxVolum(b_a201);
	v_a201 = getVariance(b_a201, max_201);

	min_202 = getMinVolum(a_a202);
	b_a202 = getSumHiVolum(a_a202, min_202);
	max_202 = getMaxVolum(b_a202);
	v_a202 = getVariance(b_a202, max_202);

	min_203 = getMinVolum(a_a203);
	b_a203 = getSumHiVolum(a_a203, min_203);
	max_203 = getMaxVolum(b_a203);
	v_a203 = getVariance(b_a203, max_203);

	min_204 = getMinVolum(a_a204);
	b_a204 = getSumHiVolum(a_a204, min_204);
	max_204 = getMaxVolum(b_a204);
	v_a204 = getVariance(b_a204, max_204);

	min_205 = getMinVolum(a_a205);
	b_a205 = getSumHiVolum(a_a205, min_205);
	max_205 = getMaxVolum(b_a205);
	v_a205 = getVariance(b_a205, max_205);

	min_206 = getMinVolum(a_a206);
	b_a206 = getSumHiVolum(a_a206, min_206);
	max_206 = getMaxVolum(b_a206);
	v_a206 = getVariance(b_a206, max_206);

	min_207 = getMinVolum(a_a207);
	b_a207 = getSumHiVolum(a_a207, min_207);
	max_207 = getMaxVolum(b_a207);
	v_a207 = getVariance(b_a207, max_207);

	min_208 = getMinVolum(a_a208);
	b_a208 = getSumHiVolum(a_a208, min_208);
	max_208 = getMaxVolum(b_a208);
	v_a208 = getVariance(b_a208, max_208);

	min_209 = getMinVolum(a_a209);
	b_a209 = getSumHiVolum(a_a209, min_209);
	max_209 = getMaxVolum(b_a209);
	v_a209 = getVariance(b_a209, max_209);

	min_300 = getMinVolum(a_a300);
	b_a300 = getSumHiVolum(a_a300, min_300);
	max_300 = getMaxVolum(b_a300);
	v_a300 = getVariance(b_a300, max_300);

	min_301 = getMinVolum(a_a301);
	b_a301 = getSumHiVolum(a_a301, min_301);
	max_301 = getMaxVolum(b_a301);
	v_a301 = getVariance(b_a301, max_301);

	b_total = b_a100[a_date.length-1] + b_a201[a_date.length-1] + b_a202[a_date.length-1] + b_a203[a_date.length-1] + b_a204[a_date.length-1] + 
			  b_a205[a_date.length-1] + b_a206[a_date.length-1] + b_a207[a_date.length-1] + b_a208[a_date.length-1] + b_a209[a_date.length-1] + 
			  b_a300[a_date.length-1] + b_a301[a_date.length-1];   
	max_total = max_100 + max_201 + max_202 + max_203 + max_204 + max_205 + max_206 + max_207 + max_208 + max_209 + max_300 + max_301;


	console.log(" b_total :  " + b_total);
	console.log(" max_total :  " + max_total);
	
}

function getTotal(i) {
	
	return b_a100[i] + b_a201[i] + b_a202[i] + b_a203[i] + b_a204[i] + b_a205[i] + b_a206[i] + b_a207[i] + b_a208[i] + b_a209[i] + b_a300[i] + b_a301[i];
}


function showList() {
	var result = [];
	var i = a_date.length-1;
	var b_total = 0;
	for (var i=a_date.length-1; i> a_date.length-10; i--) {
	   
		b_total = getTotal(i);
		result.push( { 날짜 : a_date[i],
	        개인 : Math.round((b_a100[i]/b_total).toFixed(2) * 100),
	        금융투자 : Math.round((b_a201[i]/b_total).toFixed(2) * 100),
	        보험 : Math.round((b_a202[i]/b_total).toFixed(2) * 100),
	        투신 : Math.round((b_a203[i]/b_total).toFixed(2) * 100),
	        사모 : Math.round((b_a204[i]/b_total).toFixed(2) * 100),
	        은행 : Math.round((b_a205[i]/b_total).toFixed(2) * 100),
	        연기금 : Math.round((b_a206[i]/b_total).toFixed(2) * 100),
	        기타금융 : Math.round((b_a207[i]/b_total).toFixed(2) * 100),
	        국가 : Math.round((b_a208[i]/b_total).toFixed(2) * 100),
	        기타법인 : Math.round((b_a209[i]/b_total).toFixed(2) * 100),
	        외국인 : Math.round((b_a300[i]/b_total).toFixed(2) * 100),
	        기타외국인 : Math.round((b_a301[i]/b_total).toFixed(2) * 100) });
		
	}
	
	


	result.push({
		구분 : '매수누적',
		개인 : a_a100[i],
		금융투자 : a_a201[i],
		보험 : a_a202[i],
		투신 : a_a203[i],
		사모 : a_a204[i],
		은행 : a_a205[i],
		연기금 : a_a206[i],
		기타금융 : a_a207[i],
		국가 : a_a208[i],
		기타법인 : a_a209[i],
		외국인 : a_a300[i],
		기타외국인 : a_a301[i]

	});
	
	result.push({
		구분 : '선도비중',
		개인 : Math.round((max_100/max_total).toFixed(2) * 100),
		금융투자 : Math.round((max_201/max_total).toFixed(2) * 100),
		보험 : Math.round((max_202/max_total).toFixed(2) * 100),
		투신 : Math.round((max_203/max_total).toFixed(2) * 100),
		사모 : Math.round((max_204/max_total).toFixed(2) * 100),
		은행 : Math.round((max_205/max_total).toFixed(2) * 100),
		연기금 : Math.round((max_206/max_total).toFixed(2) * 100),
		기타금융 : Math.round((max_207/max_total).toFixed(2) * 100),
		국가 : Math.round((max_208/max_total).toFixed(2)* 100),
		기타법인 : Math.round((max_209/max_total).toFixed(2) * 100),
		외국인 : Math.round((max_300/max_total).toFixed(2) * 100),
		기타외국인 : Math.round((max_301/max_total).toFixed(2) * 100)

	});
	
	for (var i=a_date.length-1; i> a_date.length-10; i--) {
	result.push({
		날짜 : a_date[i],
		개인 : v_a100[i],
		금융투자 : v_a201[i],
		보험 : v_a202[i],
		투신 : v_a203[i],
		사모 : v_a204[i],
		은행 : v_a205[i],
		연기금 : v_a206[i],
		기타금융 : v_a207[i],
		국가 : v_a208[i],
		기타법인 : v_a209[i],
		외국인 : v_a300[i],
		기타외국인 : v_a301[i]

	});
	
	}
	
//	result.push({
//		구분 : '보유비중',
//		개인 :  Math.round((b_a100[i]/b_total).toFixed(2) * 100),  // Math.round((aObj[i]/maxVal).toFixed(2) * 100);
//		금융투자 : Math.round((b_a201[i]/b_total).toFixed(2) * 100),
//		보험 : Math.round((b_a202[i]/b_total).toFixed(2) * 100),
//		투신 : Math.round((b_a203[i]/b_total).toFixed(2) * 100),
//		사모 : Math.round((b_a204[i]/b_total).toFixed(2) * 100),
//		은행 : Math.round((b_a205[i]/b_total).toFixed(2) * 100),
//		연기금 : Math.round((b_a206[i]/b_total).toFixed(2) * 100),
//		기타금융 : Math.round((b_a207[i]/b_total).toFixed(2) * 100),
//		국가 : Math.round((b_a208[i]/b_total).toFixed(2) * 100),
//		기타법인 : Math.round((b_a209[i]/b_total).toFixed(2) * 100),
//		외국인 : Math.round((b_a300[i]/b_total).toFixed(2) * 100),
//		기타외국인 : Math.round((b_a301[i]/b_total).toFixed(2) * 100)
//
//	});
	
	


	console.log(JSON.stringify(result));

}

//데이터베이스 쿼리를 사용합니다.
connection.query(query, function(error, result, fields) {

	console.log(query);

	if(error) {
		console.log('쿼리 문장에 오류가 있습니다.');
	} else {

		makeData(result);
		showList();

	}

});

connection.end();